package org.assistant.common.jdbc.core;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.assistant.common.jdbc.support.JdbcUtils;

public class JdbcTemplate {
	protected final static Log log = LogFactory.getLog(JdbcTemplate.class);
	protected final static boolean isDebugEnabled = log.isDebugEnabled();

	private DataSource dataSource;

	public JdbcTemplate() {
	}

	public JdbcTemplate(DataSource dataSource) {
		if (dataSource == null) {
			throw new IllegalArgumentException(
				"dataSource object must not be null");
		}
		this.dataSource = dataSource;
	}

	/**
	 * Methods dealing with static SQL (java.sql.Statement)
	 * 
	 * @param action
	 * @return
	 * @throws SQLException
	 */
	public <T> T execute(StatementCallback<T> action) throws SQLException {
		if (action == null) {
			throw new IllegalArgumentException(
				"Callback object must not be null");
		}

		Connection conn = null;
		Statement stmt = null;
		try {
			conn = getDataSource().getConnection();
			stmt = conn.createStatement();

			T result = action.doInStatement(stmt);
			return result;
		} catch (SQLException ex) {
			try {
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}

			throw ex;
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 执行给定的SQL语句
	 * 
	 * @param sql
	 * @throws SQLException
	 */
	public <T> void execute(final String sql) throws SQLException {
		if (isDebugEnabled) {
			log.debug("Executing SQL statement [" + sql + "]");
		}

		class ExecuteStatementCallback<E> implements StatementCallback<E> {
			public E doInStatement(Statement stmt) throws SQLException {
				stmt.execute(sql);
				return null;
			}
		}
		execute(new ExecuteStatementCallback<T>());
	}

	/**
	 * 执行给定的SQL语句，并返回由ResultSetExtractor解析的对象列表
	 * 
	 * @param sql
	 * @param rse
	 * @return
	 * @throws SQLException
	 */
	public <T> List<T> queryForList(final String sql,
			final ResultSetExtractor<T> rse) throws SQLException {
		if (sql == null) {
			throw new IllegalArgumentException("SQL must not be null");
		}
		if (rse == null) {
			throw new IllegalArgumentException(
				"ResultSetExtractor must not be null");
		}
		if (isDebugEnabled) {
			log.debug("Executing SQL query [" + sql + "]");
		}

		class QueryStatementCallback implements StatementCallback<List<T>> {
			public List<T> doInStatement(Statement stmt) throws SQLException {
				ResultSet rs = null;
				try {
					rs = stmt.executeQuery(sql);
					return rse.extractData(rs);
				} finally {
					try {
						if (rs != null) {
							rs.close();
							rs = null;
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
		return execute(new QueryStatementCallback());
	}

	/**
	 * 执行给定的SQL语句，并返回由RowMapper解析的对象列表
	 * 
	 * @param <T>
	 * @param sql
	 * @param rowMapper
	 * @return
	 * @throws SQLException
	 */
	public <T> List<T> queryForList(String sql, RowMapper<T> rowMapper)
			throws SQLException {
		return queryForList(sql, new RowMapperResultSetExtractor<T>(rowMapper));
	}

	/**
	 * 判断List中是否只有一个元素，如果是的话，则返回该元素；否则抛出异常
	 * 
	 * @param <T>
	 * @param results
	 * @return
	 */
	private <T> T requiredSingleResult(List<T> results) {
		int size = (results != null ? results.size() : 0);
		if (size == 0) {
			throw new RuntimeException("Query result's size is 0");
		}
		if (results.size() > 1) {
			throw new RuntimeException("Query result's size greater than 1");
		}
		return results.iterator().next();
	}

	/**
	 * 执行给定的SQL语句，并返回由RowMapper解析的对象
	 * 
	 * @param sql
	 * @param rowMapper
	 * @return
	 * @throws SQLException
	 */
	public <T> T queryForObject(String sql, RowMapper<T> rowMapper)
			throws SQLException {
		List<T> results = queryForList(sql, rowMapper);
		return requiredSingleResult(results);
	}

	/**
	 * 执行给定的SQL语句，并返回指定类的对象。该函数通常用于处理仅仅返回一列数据的SQL
	 * 
	 * @param sql
	 * @param requiredType
	 * @return
	 * @throws SQLException
	 */
	public <T> T queryForObject(String sql, Class<T> requiredType)
			throws SQLException {
		return queryForObject(sql, new SingleColumnRowMapper<T>(requiredType));
	}

	public long queryForLong(String sql) throws SQLException {
		Long number = queryForObject(sql, Long.class);
		return (number != null ? number.longValue() : 0);
	}

	public int queryForInt(String sql) throws SQLException {
		Integer number = queryForObject(sql, Integer.class);
		return (number != null ? number.intValue() : 0);
	}

	public <T> List<T> queryForList(String sql, Class<T> elementType)
			throws SQLException {
		return queryForList(sql, new SingleColumnRowMapper<T>(elementType));
	}

	public List<Map<String, Object>> queryForList(String sql)
			throws SQLException {
		return queryForList(sql, new ColumnMapRowMapper());
	}

	public int update(final String sql) throws SQLException {
		if (sql == null) {
			throw new IllegalArgumentException("SQL must not be null");
		}
		if (isDebugEnabled) {
			log.debug("Executing SQL update [" + sql + "]");
		}

		class UpdateStatementCallback implements StatementCallback<Integer> {
			public Integer doInStatement(Statement stmt) throws SQLException {
				int rows = stmt.executeUpdate(sql);
				if (isDebugEnabled) {
					log.debug("SQL update affected " + rows + " rows");
				}
				return new Integer(rows);
			}
		}
		return (execute(new UpdateStatementCallback())).intValue();
	}

	public int[] batchUpdate(final String[] sql) throws SQLException {
		if (sql == null) {
			throw new IllegalArgumentException("SQL must not be null");
		}
		if (isDebugEnabled) {
			log.debug("Executing SQL batch update of " + sql.length
					+ " statements");
		}

		class BatchUpdateStatementCallback implements
				StatementCallback<Integer[]> {
			public Integer[] doInStatement(Statement stmt) throws SQLException {
				int[] rowsAffected = new int[sql.length];
				if (JdbcUtils.supportsBatchUpdates(stmt.getConnection())) {
					for (int i = 0; i < sql.length; i++) {
						stmt.addBatch(sql[i]);
					}
					rowsAffected = stmt.executeBatch();
				} else {
					for (int i = 0; i < sql.length; i++) {
						if (!stmt.execute(sql[i])) {
							rowsAffected[i] = stmt.getUpdateCount();
						} else {
							throw new RuntimeException(
								"Invalid batch SQL statement: " + sql[i]);
						}
					}
				}

				Integer[] results = new Integer[rowsAffected.length];
				for (int i = 0; i < rowsAffected.length; i++) {
					results[i] = rowsAffected[i];
				}
				return results;
			}
		}
		Integer[] tmp = execute(new BatchUpdateStatementCallback());
		int[] results = new int[tmp.length];
		for (int i = 0; i < tmp.length; i++) {
			results[i] = tmp[i];
		}
		return results;
	}

	/**
	 * 内部类，用于根据传入的sql创建PreparedStatement对象
	 */
	private static class SimplePreparedStatementCreator implements
			PreparedStatementCreator {
		private final String sql;

		public SimplePreparedStatementCreator(String sql) {
			if (sql == null) {
				throw new IllegalArgumentException("SQL must not be null");
			}
			this.sql = sql;
		}

		public PreparedStatement createPreparedStatement(Connection con)
				throws SQLException {
			if (isDebugEnabled) {
				log.debug("Prepared SQL statement [" + sql + "]");
			}
			return con.prepareStatement(this.sql);
		}
	}

	/**
	 * Methods dealing with prepared statements
	 * 
	 * @param psc
	 * @param action
	 * @return
	 * @throws SQLException
	 */
	public <T> T execute(PreparedStatementCreator psc,
			PreparedStatementCallback<T> action) throws SQLException {
		if (psc == null) {
			throw new IllegalArgumentException(
				"PreparedStatementCreator must not be null");
		}
		if (action == null) {
			throw new IllegalArgumentException(
				"Callback object must not be null");
		}

		Connection con = getDataSource().getConnection();
		PreparedStatement ps = null;
		try {
			ps = psc.createPreparedStatement(con);
			T result = action.doInPreparedStatement(ps);
			return result;
		} catch (SQLException ex) {
			try {
				if (ps != null) {
					ps.close();
					ps = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
				psc = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} finally {
			try {
				if (ps != null) {
					ps.close();
					ps = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
				psc = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public <T> T execute(String sql, PreparedStatementCallback<T> action)
			throws SQLException {
		return execute(new SimplePreparedStatementCreator(sql), action);
	}

	/**
	 * Query using a prepared statement, allowing for a PreparedStatementCreator
	 * and a PreparedStatementSetter. Most other query methods use this method,
	 * but application code will always work with either a creator or a setter.
	 * 
	 * @param psc
	 *            Callback handler that can create a PreparedStatement given a
	 *            Connection
	 * @param pss
	 *            object that knows how to set values on the prepared statement.
	 *            If this is null, the SQL will be assumed to contain no bind
	 *            parameters.
	 * @param rse
	 *            object that will extract results.
	 * @return an arbitrary result object, as returned by the ResultSetExtractor
	 * @throws SQLException
	 */
	public <T> List<T> queryForList(PreparedStatementCreator psc,
			final PreparedStatementSetter pss, final ResultSetExtractor<T> rse)
			throws SQLException {
		if (rse == null) {
			throw new IllegalArgumentException(
				"ResultSetExtractor must not be null");
		}
		if (isDebugEnabled) {
			log.debug("Executing prepared SQL query");
		}

		return execute(psc, new PreparedStatementCallback<List<T>>() {
			public List<T> doInPreparedStatement(PreparedStatement ps)
					throws SQLException {
				ResultSet rs = null;
				try {
					if (pss != null) {
						pss.setValues(ps);
					}
					rs = ps.executeQuery();
					return rse.extractData(rs);
				} finally {
					if (rs != null) {
						rs.close();
						rs = null;
					}
				}
			}
		});
	}

	public <T> List<T> queryForList(PreparedStatementCreator psc,
			ResultSetExtractor<T> rse) throws SQLException {
		return queryForList(psc, null, rse);
	}

	public <T> List<T> queryForList(String sql, PreparedStatementSetter pss,
			ResultSetExtractor<T> rse) throws SQLException {
		return queryForList(new SimplePreparedStatementCreator(sql), pss, rse);
	}

	public <T> List<T> queryForList(String sql, Object[] args, int[] argTypes,
			ResultSetExtractor<T> rse) throws SQLException {
		return queryForList(sql, new ArgTypePreparedStatementSetter(
			args, argTypes), rse);
	}

	public <T> List<T> queryForList(String sql, Object[] args,
			ResultSetExtractor<T> rse) throws SQLException {
		int[] argTypes = new int[args.length];
		for (int i = 0; i < args.length; i++) {
			if (null == args[i]) {
				argTypes[i] = Types.NULL;
			} else {
				argTypes[i] = StatementCreatorUtils
					.javaTypeToSqlParameterType(args[i].getClass());
			}
		}
		return queryForList(sql, new ArgTypePreparedStatementSetter(
			args, argTypes), rse);
	}

	public <T> List<T> queryForList(PreparedStatementCreator psc,
			RowMapper<T> rowMapper) throws SQLException {
		return queryForList(psc, new RowMapperResultSetExtractor<T>(rowMapper));
	}

	public <T> List<T> queryForList(String sql, PreparedStatementSetter pss,
			RowMapper<T> rowMapper) throws SQLException {
		return queryForList(sql, pss, new RowMapperResultSetExtractor<T>(
			rowMapper));
	}

	public <T> List<T> queryForList(String sql, Object[] args, int[] argTypes,
			RowMapper<T> rowMapper) throws SQLException {
		return queryForList(
			sql, args, argTypes, new RowMapperResultSetExtractor<T>(rowMapper));
	}

	public <T> List<T> queryForList(String sql, Object[] args,
			RowMapper<T> rowMapper) throws SQLException {
		return queryForList(sql, args, new RowMapperResultSetExtractor<T>(
			rowMapper));
	}

	public <T> T queryForObject(String sql, Object[] args, int[] argTypes,
			RowMapper<T> rowMapper) throws SQLException {
		List<T> results = queryForList(
			sql, args, argTypes, new RowMapperResultSetExtractor<T>(
				rowMapper, 1));
		return requiredSingleResult(results);
	}

	public <T> T queryForObject(String sql, Object[] args,
			RowMapper<T> rowMapper) throws SQLException {
		List<T> results = queryForList(
			sql, args, new RowMapperResultSetExtractor<T>(rowMapper, 1));
		return requiredSingleResult(results);
	}

	public <T> T queryForObject(String sql, Object[] args, int[] argTypes,
			Class<T> requiredType) throws SQLException {
		return queryForObject(
			sql, args, argTypes, new SingleColumnRowMapper<T>(requiredType));
	}

	public <T> T queryForObject(String sql, Object[] args, Class<T> requiredType)
			throws SQLException {
		return queryForObject(sql, args, new SingleColumnRowMapper<T>(
			requiredType));
	}

	public Map<String, Object> queryForMap(String sql, Object[] args,
			int[] argTypes) throws SQLException {
		return queryForObject(sql, args, argTypes, new ColumnMapRowMapper());
	}

	public Map<String, Object> queryForMap(String sql, Object... args)
			throws SQLException {
		return queryForObject(sql, args, new ColumnMapRowMapper());
	}

	public long queryForLong(String sql, Object[] args, int[] argTypes)
			throws SQLException {
		Long number = queryForObject(sql, args, argTypes, Long.class);
		return (number != null ? number.longValue() : 0);
	}

	public long queryForLong(String sql, Object... args) throws SQLException {
		Long number = queryForObject(sql, args, Long.class);
		return (number != null ? number.longValue() : 0);
	}

	public int queryForInt(String sql, Object[] args, int[] argTypes)
			throws SQLException {
		Integer number = queryForObject(sql, args, argTypes, Integer.class);
		return (number != null ? number.intValue() : 0);
	}

	public int queryForInt(String sql, Object... args) throws SQLException {
		Integer number = queryForObject(sql, args, Integer.class);
		return (number != null ? number.intValue() : 0);
	}

	public <T> List<T> queryForList(String sql, Object[] args, int[] argTypes,
			Class<T> elementType) throws SQLException {
		return queryForList(sql, args, argTypes, new SingleColumnRowMapper<T>(
			elementType));
	}

	public <T> List<T> queryForList(String sql, Object[] args,
			Class<T> elementType) throws SQLException {
		return queryForList(
			sql, args, new SingleColumnRowMapper<T>(elementType));
	}

	public <T> List<T> queryForList(String sql, Object[] args, int[] argTypes)
			throws SQLException {
		return queryForList(sql, args, argTypes, new SingleColumnRowMapper<T>());
	}

	public <T> List<T> queryForList(String sql, Object... args)
			throws SQLException {
		return queryForList(sql, args, new SingleColumnRowMapper<T>());
	}

	protected int update(final PreparedStatementCreator psc,
			final PreparedStatementSetter pss) throws SQLException {
		if (isDebugEnabled) {
			log.debug("Executing prepared SQL update");
		}

		Integer result = execute(psc, new PreparedStatementCallback<Integer>() {
			public Integer doInPreparedStatement(PreparedStatement ps)
					throws SQLException {
				try {
					if (pss != null) {
						pss.setValues(ps);
					}
					int rows = -1;
					try {
						rows = ps.executeUpdate();
					} catch (Exception e) {
						e.printStackTrace();
					}
					if (isDebugEnabled) {
						log.debug("SQL update affected " + rows + " rows");
					}
					return new Integer(rows);
				} finally {
					try {
						if (ps != null) {
							ps.close();
							ps = null;
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		});
		if (null == result) {
			return -1;
		}
		return result.intValue();
	}

	public int update(PreparedStatementCreator psc) throws SQLException {
		return update(psc, (PreparedStatementSetter) null);
	}

	public int update(String sql, PreparedStatementSetter pss)
			throws SQLException {
		return update(new SimplePreparedStatementCreator(sql), pss);
	}

	public int update(String sql, Object[] args, int[] argTypes)
			throws SQLException {
		return update(sql, new ArgTypePreparedStatementSetter(args, argTypes));
	}

	public int update(String sql, Object... args) throws SQLException {
		if (args == null || args.length == 0) {
			return update(sql);
		} else {
			int[] argTypes = new int[args.length];
			for (int i = 0; i < args.length; i++) {
				if (null == args[i]) {
					argTypes[i] = Types.NULL;
				} else {
					argTypes[i] = StatementCreatorUtils
						.javaTypeToSqlParameterType(args[i].getClass());
				}
			}
			return update(sql, new ArgTypePreparedStatementSetter(
				args, argTypes));
		}
	}

	public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss)
			throws SQLException {
		if (isDebugEnabled) {
			log.debug("Executing SQL batch update [" + sql + "]");
		}

		return execute(sql, new PreparedStatementCallback<int[]>() {
			public int[] doInPreparedStatement(PreparedStatement ps)
					throws SQLException {
				int batchSize = pss.getBatchSize();
				InterruptibleBatchPreparedStatementSetter ipss = (pss instanceof InterruptibleBatchPreparedStatementSetter ? (InterruptibleBatchPreparedStatementSetter) pss
						: null);
				if (JdbcUtils.supportsBatchUpdates(ps.getConnection())) {
					for (int i = 0; i < batchSize; i++) {
						pss.setValues(ps, i);
						if (ipss != null && ipss.isBatchExhausted(i)) {
							break;
						}
						ps.addBatch();
					}
					return ps.executeBatch();
				} else {
					List<Integer> rowsAffected = new ArrayList<Integer>();
					for (int i = 0; i < batchSize; i++) {
						pss.setValues(ps, i);
						if (ipss != null && ipss.isBatchExhausted(i)) {
							break;
						}
						rowsAffected.add(new Integer(ps.executeUpdate()));
					}
					int[] rowsAffectedArray = new int[rowsAffected.size()];
					for (int i = 0; i < rowsAffectedArray.length; i++) {
						rowsAffectedArray[i] = ((Integer) rowsAffected.get(i))
							.intValue();
					}
					return rowsAffectedArray;
				}
			}
		});
	}

	// 下面是类似Spring中的SimpleJdbcTemplate的接口

	public <T> T queryForObject(String sql, Class<T> requiredType,
			Object... args) throws SQLException {
		return (args == null || args.length == 0) ? queryForObject(
			sql, requiredType) : queryForObject(sql, args, requiredType);
	}

	public <T> T queryForObject(String sql, RowMapper<T> rm, Object... args)
			throws SQLException {
		return (args == null || args.length == 0) ? queryForObject(sql, rm)
				: queryForObject(sql, getArguments(args), rm);
	}

	public <T> List<T> queryForList(String sql, RowMapper<T> rm, Object... args)
			throws SQLException {
		return (args == null || args.length == 0) ? queryForList(sql, rm)
				: queryForList(sql, getArguments(args), rm);
	}

	public int[] batchUpdate(String sql, List<Object[]> batchArgs)
			throws SQLException {
		return doExecuteBatchUpdate(sql, batchArgs, new int[0]);
	}

	public int[] batchUpdate(String sql, List<Object[]> batchArgs,
			int[] argTypes) throws SQLException {
		return doExecuteBatchUpdate(sql, batchArgs, argTypes);
	}

	private int[] doExecuteBatchUpdate(String sql,
			final List<Object[]> batchValues, final int[] columnTypes)
			throws SQLException {
		return batchUpdate(sql, new BatchPreparedStatementSetter() {
			public void setValues(PreparedStatement ps, int i)
					throws SQLException {
				Object[] values = batchValues.get(i);
				doSetStatementParameters(values, ps, columnTypes);
			}

			public int getBatchSize() {
				return batchValues.size();
			}
		});
	}

	private void doSetStatementParameters(Object[] values,
			PreparedStatement ps, int[] columnTypes) throws SQLException {
		int colIndex = 0;
		for (Object value : values) {
			colIndex++;
			int colType;
			if (columnTypes == null || columnTypes.length < colIndex) {
				colType = JdbcUtils.TYPE_UNKNOWN;
			} else {
				colType = columnTypes[colIndex - 1];
			}
			StatementCreatorUtils.setParameterValue(
				ps, colIndex, colType, value);
		}
	}

	private Object[] getArguments(Object[] varArgs) {
		if (varArgs.length == 1 && varArgs[0] instanceof Object[]) {
			return (Object[]) varArgs[0];
		} else {
			return varArgs;
		}
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
}
